<!DOCTYPE html>


<html lang="zh-CN">
  

    <head>
      <meta charset="utf-8" />
        
      <meta name="description" content="加油，未来可期！" />
      
      <meta
        name="viewport"
        content="width=device-width, initial-scale=1, maximum-scale=1"
      />
      <title>mysql基础 |  王先生的博客</title>
  <meta name="generator" content="hexo-theme-ayer">
      
      <link rel="shortcut icon" href="/favicon.ico" />
       
<link rel="stylesheet" href="/dist/main.css">

      
<link rel="stylesheet" href="/css/fonts/remixicon.css">

      
<link rel="stylesheet" href="/css/custom.css">
 
      <script src="https://cdn.staticfile.org/pace/1.2.4/pace.min.js"></script>
       
 

      <link
        rel="stylesheet"
        href="https://cdn.jsdelivr.net/npm/@sweetalert2/theme-bulma@5.0.1/bulma.min.css"
      />
      <script src="https://cdn.jsdelivr.net/npm/sweetalert2@11.0.19/dist/sweetalert2.min.js"></script>

      <!-- mermaid -->
      
      <style>
        .swal2-styled.swal2-confirm {
          font-size: 1.6rem;
        }
      </style>
    <link rel="alternate" href="/atom.xml" title="王先生的博客" type="application/atom+xml">
</head>
  </html>
</html>


<body>
  <div id="app">
    
      
    <main class="content on">
      <section class="outer">
  <article
  id="post-mysql基础"
  class="article article-type-post"
  itemscope
  itemprop="blogPost"
  data-scroll-reveal
>
  <div class="article-inner">
    
    <header class="article-header">
       
<h1 class="article-title sea-center" style="border-left:0" itemprop="name">
  mysql基础
</h1>
 

      
    </header>
     
    <div class="article-meta">
      <a href="/2022/05/29/mysql%E5%9F%BA%E7%A1%80/" class="article-date">
  <time datetime="2022-05-29T14:29:22.000Z" itemprop="datePublished">2022-05-29</time>
</a> 
  <div class="article-category">
    <a class="article-category-link" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a>
  </div>
  
<div class="word_count">
    <span class="post-time">
        <span class="post-meta-item-icon">
            <i class="ri-quill-pen-line"></i>
            <span class="post-meta-item-text"> 字数统计:</span>
            <span class="post-count">10.2k</span>
        </span>
    </span>

    <span class="post-time">
        &nbsp; | &nbsp;
        <span class="post-meta-item-icon">
            <i class="ri-book-open-line"></i>
            <span class="post-meta-item-text"> 阅读时长≈</span>
            <span class="post-count">43 分钟</span>
        </span>
    </span>
</div>
 
    </div>
      
    <div class="tocbot"></div>




  
    <div class="article-entry" itemprop="articleBody">
       
  <h1 id="数据库"><a href="#数据库" class="headerlink" title="数据库"></a>数据库</h1><ul>
<li><p>数据库分类</p>
<ol>
<li><p>关系型数据库(RDBMS)</p>
<p>概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库</p>
<p>特点：</p>
<ol>
<li>使用表存储数据，格式统一，便于维护</li>
<li>使用SQL语言操作，标准统一，使用方便</li>
</ol>
</li>
<li><p>非关系性数据库：<br>概念：不是使用表来构建的数据库。比如HIve以行的模式。redis键值对</p>
</li>
</ol>
</li>
<li><p>MySQL数据库的数据模型</p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220405160611509.png" alt="image-20220405160611509"></p>
</li>
</ul>
<h2 id="SQL"><a href="#SQL" class="headerlink" title="SQL"></a>SQL</h2><p>分为：</p>
<ul>
<li>SQL的通用语法</li>
<li>SQL的分类</li>
<li>DDL</li>
<li>DML</li>
<li>DQL</li>
<li>DCL</li>
</ul>
<h3 id="SQL的通用语法"><a href="#SQL的通用语法" class="headerlink" title="SQL的通用语法"></a>SQL的通用语法</h3><ol>
<li>SQL语句可以单行或者多行书写，以分号结尾</li>
<li>SQL语句可以使用空格&#x2F;缩进来增强语句的可读性</li>
<li>Mysql数据库的SQL语句不分大小写，关键字建议使用大写</li>
<li>注释：<ul>
<li>单行注释：<code>-- 注释内容</code> 或者<code># 注释内容</code></li>
<li>多行注释： <code>/*注释内容*/</code></li>
</ul>
</li>
</ol>
<h3 id="SQL的分类"><a href="#SQL的分类" class="headerlink" title="SQL的分类"></a>SQL的分类</h3><table>
<thead>
<tr>
<th>分类</th>
<th>全称</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>DDL</td>
<td>Data Definition Language</td>
<td>数据定义语言，用来定义数据库对象(数据库、表，字段)</td>
</tr>
<tr>
<td>DML</td>
<td>Data Manipulation Language</td>
<td>数据操作语言，用来对数据库表中的数据进行增删改</td>
</tr>
<tr>
<td>DQL</td>
<td>Data Query Languaue</td>
<td>数据查询语言，用来对数据库表中的数据进行查询操作</td>
</tr>
<tr>
<td>DCL</td>
<td>Data Control Language</td>
<td>数据控制语言，用来创建数据库用户、控制数据库的访问权限</td>
</tr>
</tbody></table>
<h3 id="Mysql的基础数据类型"><a href="#Mysql的基础数据类型" class="headerlink" title="Mysql的基础数据类型"></a>Mysql的基础数据类型</h3><p><strong>MySQL的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型</strong></p>
<h4 id="数值类型"><a href="#数值类型" class="headerlink" title="数值类型"></a>数值类型</h4><table>
<thead>
<tr>
<th>类型</th>
<th>大小</th>
<th>有符号范围</th>
<th>无符号范围</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>TINYINT</td>
<td>1byte</td>
<td>(-128~127)</td>
<td>(0~255)</td>
<td>小整数值</td>
</tr>
<tr>
<td>smallint</td>
<td>2bytes</td>
<td>(-32768~32767)</td>
<td>(0~65535)</td>
<td>大整数值</td>
</tr>
<tr>
<td>mediumint</td>
<td>3bytes</td>
<td>(-2^23~2^23-1)</td>
<td>(0~2^24-1)</td>
<td>大整数值</td>
</tr>
<tr>
<td>int或integer</td>
<td>4bytes</td>
<td>(-2^31~2^31-1)</td>
<td>(0~2^32-1)</td>
<td>大整数值</td>
</tr>
<tr>
<td>bigint</td>
<td>8bytes</td>
<td>(-2^63~2^63-1)</td>
<td>(0~2^64-1)</td>
<td>极大整数值</td>
</tr>
<tr>
<td>Float</td>
<td>4bytes</td>
<td></td>
<td></td>
<td>单精度浮点数</td>
</tr>
<tr>
<td>Double</td>
<td>8bytes</td>
<td></td>
<td></td>
<td>双精度浮点数</td>
</tr>
<tr>
<td>decimal</td>
<td></td>
<td>依赖于M(精度)和D(标度)的值</td>
<td>依赖于M(精度)和D(标度)的值</td>
<td>小数值(精确定点数)</td>
</tr>
</tbody></table>
<p><strong>使用：如果是age 则可以在定义时使用，age tinyint unsigned</strong></p>
<p>原因：因为age一般在100以内，使用int也可以表示，但是浪费3个字节。此外年龄没有负数，所以使用无符号</p>
<p>score： 定于： score double(4,1)   其中4代表长度，1代表位数(小数位)。</p>
<h4 id="字符串类型"><a href="#字符串类型" class="headerlink" title="字符串类型"></a>字符串类型</h4><table>
<thead>
<tr>
<th>类型</th>
<th>大小</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>CHAR</td>
<td>0-255bytes</td>
<td>定长字符串</td>
</tr>
<tr>
<td>VARCHAR</td>
<td>0-65535bytes</td>
<td>变长字符串</td>
</tr>
<tr>
<td>TINYBLOB</td>
<td>0-255bytes</td>
<td>不超过255个字符的二进制数据</td>
</tr>
<tr>
<td>TINYTEXT</td>
<td>0-255bytes</td>
<td>短文本字符串</td>
</tr>
<tr>
<td>BLOB</td>
<td>0-65535bytes</td>
<td>二进制形式的长文本数据</td>
</tr>
<tr>
<td>TEXT</td>
<td>0-65535bytes</td>
<td>长文本数据</td>
</tr>
<tr>
<td>MEDIUMBLOB</td>
<td>0-16777215bytes</td>
<td>二进制形式的中等长度文本数据</td>
</tr>
<tr>
<td>MEDIUMTEXT</td>
<td>0-16777215bytes</td>
<td>中等长度文本数据</td>
</tr>
<tr>
<td>LONGBLOB</td>
<td>0-4294967295bytes</td>
<td>二进制形式的极大文本数据</td>
</tr>
<tr>
<td>LONGTEXT</td>
<td>0-4294967295bytes</td>
<td>极大本文数据</td>
</tr>
</tbody></table>
<p><strong>定长和变长的字符串</strong></p>
<ol>
<li><p>可以标明长度</p>
<p>char(10):  表示可以存储10个字符的定长字符串。不管存储几个字符都占10个字符的位置，没有用0补</p>
<p>varchar(10): 表示可以存储10个字符的定长字符串。使用几个字符占几个字符的位置</p>
</li>
<li><p>对比：</p>
<ol>
<li>char： 性能高</li>
<li>varchar： 性能低，因为需要根据内容计算长度</li>
</ol>
</li>
<li><p>使用推荐：</p>
<ul>
<li>用户名：varchar(50)。 推荐varchar因为不确定用户输入的用户名长</li>
<li>性别：char(1)。 因为知道性别不是男就是女，所以使用定长就可以，性能反而很高</li>
</ul>
</li>
</ol>
<h4 id="日期时间类型"><a href="#日期时间类型" class="headerlink" title="日期时间类型"></a>日期时间类型</h4><table>
<thead>
<tr>
<th>类型</th>
<th>大小</th>
<th>范围</th>
<th>格式</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>DATE</td>
<td>3</td>
<td>1000-01-01至9999-12-31</td>
<td>YYYY-MM-DD</td>
<td>日期值</td>
</tr>
<tr>
<td>TIME</td>
<td>3</td>
<td>-838:59:59至838:59:59</td>
<td>HH:MM:SS</td>
<td>时间值或持续时间</td>
</tr>
<tr>
<td>YEAR</td>
<td>1</td>
<td>1901至2155</td>
<td>YYYY</td>
<td>年份值</td>
</tr>
<tr>
<td>DATETIME</td>
<td>8</td>
<td>1000-01至9999-12-31</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>混合日期和时间值</td>
</tr>
<tr>
<td>TIMESTAMP</td>
<td>4</td>
<td>1970-01至2038-01</td>
<td>YYYY-MM-DD HH:MM:SS</td>
<td>混合日期和时间值，时间戳</td>
</tr>
</tbody></table>
<h4 id="案例"><a href="#案例" class="headerlink" title="案例"></a>案例</h4><p>设计一张员工表，要求如下：</p>
<ol>
<li>编号，纯数字</li>
<li>员工工号(字符串长度，长度不超过10位)</li>
<li>员工姓名（字符串长度，长度不超过10位）</li>
<li>性别（男&#x2F;女，存储一个汉字）</li>
<li>年龄（正常人年龄，不可能存储负数）</li>
<li>身份证号（二代身份证均为18位，身份证中含有X）</li>
<li>入职时间（取值年月日）</li>
</ol>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">crate <span class="keyword">table</span> employee(</span><br><span class="line">id <span class="type">int</span>,</span><br><span class="line">eno <span class="type">varchar</span>(<span class="number">10</span>),</span><br><span class="line">name <span class="type">varchar</span>(<span class="number">10</span>),</span><br><span class="line">gender <span class="type">char</span>(<span class="number">1</span>),</span><br><span class="line">age tinyint unsigned,</span><br><span class="line">idcard <span class="type">char</span>(<span class="number">18</span>),</span><br><span class="line">hiretime <span class="type">date</span></span><br><span class="line">)</span><br></pre></td></tr></table></figure>



<h3 id="DDL"><a href="#DDL" class="headerlink" title="DDL"></a>DDL</h3><p><strong>数据定义语言</strong>，用来操作数据库、数据表、字段的定义</p>
<ul>
<li><p>数据库相关操作</p>
<ol>
<li><p>查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 查询所有数据库</span><br><span class="line"><span class="keyword">show</span> databases;</span><br><span class="line"># 查询当前数据库</span><br><span class="line"><span class="keyword">select</span> databases();</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"># 语法：<span class="keyword">create</span> database [IF <span class="keyword">NOT</span> <span class="keyword">EXISTS</span>] 数据库名 [<span class="keyword">DEFAULT</span> CHARSET 字符集] [<span class="keyword">COLLATE</span> 排序规则]</span><br><span class="line"><span class="keyword">create</span> database myData;# 创建数据库名为myData的数据库</span><br><span class="line"><span class="keyword">create</span> database test <span class="keyword">default</span> charset utf8mb4; # utf8mb4支持四个字节存储，建议使用此字符集</span><br></pre></td></tr></table></figure>


</li>
<li><p>删除</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 语法：<span class="keyword">drop</span> database [IF <span class="keyword">EXISTS</span>] 数据库名;</span><br><span class="line"><span class="keyword">drop</span> database if <span class="keyword">exists</span> myData;</span><br></pre></td></tr></table></figure>


</li>
<li><p>使用</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 使用</span><br><span class="line">use 数据库名；</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
<li><p>数据表相关操作</p>
<ol>
<li><p>查询</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"># 查询当前数据库的所有表</span><br><span class="line"><span class="keyword">show</span> tables;</span><br><span class="line"># 查询表结构</span><br><span class="line"><span class="keyword">desc</span> 表名字；</span><br><span class="line"># 查询指定表的建表语句</span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> 表名；</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">	字段<span class="number">1</span> 字段<span class="number">1</span>类型 [comment 字段<span class="number">1</span>注释],</span><br><span class="line">    字段<span class="number">2</span> 字段<span class="number">2</span>类型 [comment 字段<span class="number">2</span>注释],</span><br><span class="line">    ......,</span><br><span class="line">    字段n 字段n类型[comment 字段n注释]</span><br><span class="line">)[comment 表注释];</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改表结构(修改字段)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 添加字段</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> 字段名 字段类型(长度) [comment 注释] [约束];</span><br><span class="line"># 修改数据类型</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 modify 字段名 新数据类型(长度)</span><br><span class="line"># 修改数据类型和字段名</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];</span><br><span class="line"># 删除字段</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">drop</span> 字段名；</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改表名</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 rename <span class="keyword">to</span> 新表名；</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除表</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"># 删除表</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> [IF <span class="keyword">exists</span>] 表名；</span><br><span class="line"># 删除指定表，并重新创建该表（其实就是清空数据）</span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> 表名；</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
</ul>
<h3 id="DML"><a href="#DML" class="headerlink" title="DML"></a>DML</h3><p><strong>数据操作语言</strong>，用来对数据库中表的数据记录进行增删改操作</p>
<p><strong>添加数据</strong>： insert into</p>
<p><strong>删除数据</strong>： delete</p>
<p><strong>修改数据</strong>： update</p>
<h4 id="添加数据"><a href="#添加数据" class="headerlink" title="添加数据"></a>添加数据</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># 给指定字段添加数据</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名(字段<span class="number">1</span>，字段<span class="number">2</span>,...) <span class="keyword">values</span> (值<span class="number">1</span>，值<span class="number">2</span>，...) # 字段名和值一一对应</span><br><span class="line"># 给全部字段添加数据</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名 <span class="keyword">values</span> (值<span class="number">1</span>，值<span class="number">2</span>，...) # 这里的值<span class="number">1</span>，值<span class="number">2</span>与表的结构中的字段名，一一对应</span><br><span class="line"># 批量添加数据</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名(字段<span class="number">1</span>，字段<span class="number">2</span>，...) <span class="keyword">values</span> (值<span class="number">1</span>，值<span class="number">2</span>，...),(值<span class="number">1</span>，值<span class="number">2</span>，...),(值<span class="number">1</span>，值<span class="number">2</span>，...)</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 表名 <span class="keyword">values</span> (值<span class="number">1</span>，值<span class="number">2</span>，...),(值<span class="number">1</span>，值<span class="number">2</span>，...),(值<span class="number">1</span>，值<span class="number">2</span>，...)</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ol>
<li>插入数据时，指定的字段和需要的值需要一一对应</li>
<li>字符串类型和日期型数据应该包含在引号中</li>
<li>插入的数据大小，应该在字段的规定范围内</li>
</ol>
<h4 id="修改数据"><a href="#修改数据" class="headerlink" title="修改数据"></a>修改数据</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 修改数据</span><br><span class="line"><span class="keyword">update</span> 表名 <span class="keyword">set</span> 字段<span class="number">1</span><span class="operator">=</span>值<span class="number">1</span>，字段<span class="number">2</span><span class="operator">=</span>值<span class="number">2</span>，... [<span class="keyword">where</span> 条件];</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ol>
<li>修改语句的条件可以有，也可以没有，如果没有条件，会修改整个表中的所有数据</li>
</ol>
<h4 id="删除数据"><a href="#删除数据" class="headerlink" title="删除数据"></a>删除数据</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 删除数据</span><br><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 表名 [<span class="keyword">where</span> 条件];</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ol>
<li>delete的条件可以可有可无，如果没有删除表中所有数据</li>
<li>delete语句不能删除某一个字段的值。此时建议使用<strong>update</strong>语句，将其要删除的某个字段的值设置为<code>null</code></li>
</ol>
<h3 id="DQL"><a href="#DQL" class="headerlink" title="DQL"></a>DQL</h3><p><strong>数据查询语言</strong>： 用来查询数据库中表的记录。使用频率最高~！关键字select </p>
<p><strong>DQL的语法格式</strong></p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220406180959332.png" alt="image-20220406180959332"></p>
<p><strong>常见的几种查询</strong></p>
<ul>
<li>基本查询</li>
<li>条件查询(where)</li>
<li>聚合函数(count,max,min,avg,sum)</li>
<li>分组查询（group by）</li>
<li>排序查询（order by）</li>
<li>分页查询（limit）</li>
</ul>
<p><strong>下属sql为案例的建表语句</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br></pre></td><td class="code"><pre><span class="line"># 选中数据库</span><br><span class="line"><span class="keyword">create</span> database if <span class="keyword">not</span> <span class="keyword">exists</span> myTest <span class="keyword">default</span> charset<span class="operator">=</span>utf8mb4;</span><br><span class="line"># 进入新创建的数据库</span><br><span class="line">use myTest;</span><br><span class="line"># 创建数据库</span><br><span class="line"><span class="keyword">create</span> if <span class="keyword">not</span> <span class="keyword">exists</span> <span class="keyword">table</span> emp(</span><br><span class="line">    id <span class="type">int</span> comment <span class="string">&#x27;编号&#x27;</span>,</span><br><span class="line">    workno <span class="type">varchar</span>(<span class="number">10</span>) comment <span class="string">&#x27;工号&#x27;</span>,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>) comment <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">    gender <span class="type">char</span>(<span class="number">1</span>) comment <span class="string">&#x27;性别&#x27;</span>,</span><br><span class="line">    age tinyint unsigned comment <span class="string">&#x27;年龄&#x27;</span>,</span><br><span class="line">    idcard <span class="type">char</span>(<span class="number">18</span>) comment <span class="string">&#x27;身份证号&#x27;</span>,</span><br><span class="line">    workaddress <span class="type">varchar</span>(<span class="number">50</span>) comment <span class="string">&#x27;工作地址&#x27;</span>,</span><br><span class="line">    entrydate <span class="type">date</span> comment <span class="string">&#x27;入职时间&#x27;</span></span><br><span class="line">)comment <span class="string">&#x27;员工表&#x27;</span>;</span><br><span class="line"></span><br><span class="line"># 插入数据</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> emp</span><br><span class="line"><span class="keyword">values</span></span><br><span class="line">    (<span class="number">1</span>,<span class="string">&#x27;1&#x27;</span>,<span class="string">&#x27;刘岩&#x27;</span>,<span class="string">&#x27;女&#x27;</span>,<span class="number">23</span>,<span class="string">&#x27;012345678987654321&#x27;</span>,<span class="string">&#x27;北京&#x27;</span>,<span class="string">&#x27;2000-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">2</span>,<span class="string">&#x27;2&#x27;</span>,<span class="string">&#x27;张无忌&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">26</span>,<span class="string">&#x27;013345678987654321&#x27;</span>,<span class="string">&#x27;河北&#x27;</span>,<span class="string">&#x27;2004-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">3</span>,<span class="string">&#x27;3&#x27;</span>,<span class="string">&#x27;韦一笑&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;112345678987654321&#x27;</span>,<span class="string">&#x27;上海&#x27;</span>,<span class="string">&#x27;1995-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">4</span>,<span class="string">&#x27;4&#x27;</span>,<span class="string">&#x27;赵敏&#x27;</span>,<span class="string">&#x27;女&#x27;</span>,<span class="number">38</span>,<span class="string">&#x27;052345678987654321&#x27;</span>,<span class="string">&#x27;北京&#x27;</span>,<span class="string">&#x27;1995-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">5</span>,<span class="string">&#x27;5&#x27;</span>,<span class="string">&#x27;小昭&#x27;</span>,<span class="string">&#x27;女&#x27;</span>,<span class="number">23</span>,<span class="string">&#x27;012345678987654321&#x27;</span>,<span class="string">&#x27;北京&#x27;</span>,<span class="string">&#x27;2022-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">6</span>,<span class="string">&#x27;6&#x27;</span>,<span class="string">&#x27;杨逍&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">26</span>,<span class="string">&#x27;013345678987654321&#x27;</span>,<span class="string">&#x27;河北&#x27;</span>,<span class="string">&#x27;2014-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">7</span>,<span class="string">&#x27;7&#x27;</span>,<span class="string">&#x27;樊亮亮&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">18</span>,<span class="string">&#x27;112345678987654321&#x27;</span>,<span class="string">&#x27;上海&#x27;</span>,<span class="string">&#x27;1934-01-05&#x27;</span>),</span><br><span class="line">    (<span class="number">8</span>,<span class="string">&#x27;8&#x27;</span>,<span class="string">&#x27;张士诚&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">78</span>,<span class="string">&#x27;052345878987654321&#x27;</span>,<span class="string">&#x27;北京&#x27;</span>,<span class="string">&#x27;1988-01-05&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> emp <span class="keyword">values</span> (<span class="number">9</span>,<span class="string">&#x27;9&#x27;</span>,<span class="string">&#x27;卢登&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="keyword">null</span>,<span class="string">&#x27;052345878987654321&#x27;</span>,<span class="string">&#x27;日本&#x27;</span>,<span class="string">&#x27;1983-01-05&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> emp <span class="keyword">values</span> (<span class="number">10</span>,<span class="string">&#x27;10&#x27;</span>,<span class="string">&#x27;安娜卡尔&#x27;</span>,<span class="string">&#x27;女&#x27;</span>,<span class="number">14</span>,<span class="string">&#x27;052345878987654321&#x27;</span>,<span class="string">&#x27;美国&#x27;</span>,<span class="string">&#x27;2022-01-05&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> emp <span class="keyword">values</span> (<span class="number">11</span>,<span class="string">&#x27;11&#x27;</span>,<span class="string">&#x27;女巫&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">21</span>,<span class="string">&#x27;052345878987654321&#x27;</span>,<span class="string">&#x27;法国&#x27;</span>,<span class="string">&#x27;2014-01-05&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> emp <span class="keyword">values</span> (<span class="number">12</span>,<span class="string">&#x27;12&#x27;</span>,<span class="string">&#x27;张三丰&#x27;</span>,<span class="string">&#x27;男&#x27;</span>,<span class="number">88</span>,<span class="string">&#x27;05234587898765432X&#x27;</span>,<span class="string">&#x27;重阳&#x27;</span>,<span class="string">&#x27;1977-01-05&#x27;</span>);</span><br></pre></td></tr></table></figure>



<h4 id="基本查询"><a href="#基本查询" class="headerlink" title="基本查询"></a>基本查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># <span class="number">1.</span> 查询多个字段</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名; # 查询表中所有的字段信息，不建议使用，不管是阅读性和执行的性能都不好</span><br><span class="line"><span class="keyword">select</span> 字段<span class="number">1</span>，字段<span class="number">2</span>，字段<span class="number">3</span>，... <span class="keyword">from</span> 表名; # 建议使用</span><br><span class="line"># <span class="number">2.</span> 设置别名。可以省略<span class="keyword">as</span></span><br><span class="line"><span class="keyword">select</span> 字段<span class="number">1</span> [<span class="keyword">as</span> 别名<span class="number">1</span>],字段<span class="number">2</span> [<span class="keyword">as</span> 别名<span class="number">2</span>],... <span class="keyword">from</span> 表名;</span><br><span class="line"><span class="keyword">select</span> 字段<span class="number">1</span> 别名<span class="number">1</span>，字段<span class="number">2</span> 别名<span class="number">2</span>,... <span class="keyword">from</span> 表名 ;</span><br><span class="line"># <span class="number">3.</span> 去除重复记录</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> 字段列表 <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>

<p><strong>基本案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"># 基本查询</span><br><span class="line">#<span class="comment">----- 1. 查询指定字段，name,workno,age 返回</span></span><br><span class="line"><span class="keyword">select</span> name <span class="keyword">as</span> <span class="string">&#x27;姓名&#x27;</span>, workno <span class="keyword">as</span> <span class="string">&#x27;工号&#x27;</span>,age <span class="keyword">as</span> <span class="string">&#x27;年龄&#x27;</span> <span class="keyword">from</span> emp;</span><br><span class="line">#<span class="comment">----- 2. 查询所有字段</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line">#<span class="comment">----- 3. 查询所有员工的工作地址，起别名</span></span><br><span class="line"><span class="keyword">select</span> name <span class="keyword">as</span> <span class="string">&#x27;姓名&#x27;</span>, workaddress <span class="keyword">as</span> <span class="string">&#x27;工作地址&#x27;</span> <span class="keyword">from</span> emp;</span><br><span class="line"><span class="keyword">select</span> workaddress  <span class="string">&#x27;工作地址&#x27;</span> <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line">#<span class="comment">----- 4.  查询公司员工的上班地址，不重复</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> workaddress <span class="keyword">as</span> <span class="string">&#x27;工作地址&#x27;</span> <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>



<h4 id="条件查询"><a href="#条件查询" class="headerlink" title="条件查询"></a>条件查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 基本语法结构</span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件列表</span><br></pre></td></tr></table></figure>

<p><strong>条件</strong></p>
<ul>
<li>比较运算符<ol>
<li><code>&gt;</code>,<code>&lt;</code>,<code>&gt; =</code>,<code>&lt; =</code>，<code>&lt;&gt; 或者 ! =</code>，<code>=</code></li>
<li>between 最小值(包含) and 最大值(包含)： 符合最小值到最大值的数据记录</li>
<li>in(x1,x2,x3) ：值等于x1,x2,x3的记录</li>
<li>like 占位符：模糊匹配。<code>_匹配单个字符，%匹配多个字符</code></li>
<li>is null： 字段的值为空</li>
<li>is not null： 字段的值不为空</li>
</ol>
</li>
<li>逻辑运算符<ul>
<li><code>and 或 &amp;&amp; </code>: 逻辑与，并且，多个条件同时成立</li>
<li><code>or 或 ||</code>: 逻辑或，或者，多个条件有一成立</li>
<li><code>not 或 ！</code> ： 逻辑非，不是</li>
</ul>
</li>
</ul>
<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br></pre></td><td class="code"><pre><span class="line"># 条件查询</span><br><span class="line">#<span class="comment">---- 1. 查询年龄等于 18 的员工</span></span><br><span class="line"><span class="keyword">select</span> name,age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">18</span>;</span><br><span class="line">#<span class="comment">---- 2. 查询年龄小于 20 的员工</span></span><br><span class="line"><span class="keyword">select</span> name,age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&lt;</span> <span class="number">20</span>;</span><br><span class="line">#<span class="comment">---- 3. 查询年龄小于等于 23 的员工</span></span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&lt;=</span> <span class="number">23</span> <span class="keyword">order</span> <span class="keyword">by</span> age;</span><br><span class="line">#<span class="comment">---- 4. 查询没有年龄的员工</span></span><br><span class="line"><span class="keyword">select</span> name , age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line">#<span class="comment">---- 5. 查询有年龄的员工</span></span><br><span class="line"><span class="keyword">select</span> name ,age <span class="keyword">from</span> emp <span class="keyword">where</span>  age <span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">null</span>;</span><br><span class="line">#<span class="comment">---- 6. 查询年龄不等于23的员工</span></span><br><span class="line"><span class="keyword">select</span> name,age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&lt;&gt;</span> <span class="number">23</span> <span class="keyword">order</span> <span class="keyword">by</span> age;</span><br><span class="line">#<span class="comment">---- 7. 查询年龄在18岁(包含)到23岁的员工</span></span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="keyword">between</span> <span class="number">18</span> <span class="keyword">and</span> <span class="number">23</span> <span class="keyword">order</span> <span class="keyword">by</span> age;</span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp <span class="keyword">where</span> age<span class="operator">&gt;=</span><span class="number">18</span> <span class="keyword">and</span> age <span class="operator">&lt;=</span> <span class="number">23</span> <span class="keyword">order</span> <span class="keyword">by</span> age;</span><br><span class="line">#<span class="comment">---- 8. 查询性别为女 且年龄小于 25岁的员工</span></span><br><span class="line"><span class="keyword">select</span> name,age, gender <span class="keyword">from</span> emp <span class="keyword">where</span> gender<span class="operator">=</span><span class="string">&#x27;女&#x27;</span> <span class="keyword">and</span> age<span class="operator">&lt;</span> <span class="number">25</span>;</span><br><span class="line"><span class="keyword">select</span> name,age, gender <span class="keyword">from</span> emp <span class="keyword">where</span> gender<span class="operator">=</span><span class="string">&#x27;女&#x27;</span>;</span><br><span class="line">#<span class="comment">---- 9. 查询有年龄等于18或23 的员工</span></span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="keyword">in</span> (<span class="number">14</span>,<span class="number">21</span>);</span><br><span class="line"><span class="keyword">select</span> name ,age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">14</span> <span class="keyword">or</span> age <span class="operator">=</span> <span class="number">38</span>;</span><br><span class="line">#<span class="comment">---- 10. 查询姓名为两个字的员工</span></span><br><span class="line"><span class="keyword">select</span> name,age <span class="keyword">from</span> emp <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;____&#x27;</span>;</span><br><span class="line">#<span class="comment">---- 11. 查询当前身份证尾号为X的员工。 使用17个下划线也可以，但是%号简单</span></span><br><span class="line"><span class="keyword">select</span> name, age, idcard <span class="keyword">from</span> emp <span class="keyword">where</span> idcard <span class="keyword">like</span> <span class="string">&#x27;%X&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h4 id="聚合函数"><a href="#聚合函数" class="headerlink" title="聚合函数"></a>聚合函数</h4><p><strong>介绍：</strong>将一列数据作为一个整体，进行纵向计算。注意实对列进行操作</p>
<table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="center">count</td>
<td align="center">统计数量</td>
</tr>
<tr>
<td align="center">max</td>
<td align="center">此列最大值</td>
</tr>
<tr>
<td align="center">min</td>
<td align="center">此列最小值</td>
</tr>
<tr>
<td align="center">avg</td>
<td align="center">此列平均值</td>
</tr>
<tr>
<td align="center">sum</td>
<td align="center">此列的和</td>
</tr>
</tbody></table>
<p><strong>注意：</strong>如果该列有空值，不参与聚合函数的计算</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 基础语法</span><br><span class="line"><span class="keyword">select</span> 聚合函数(字段列表) <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>

<p><strong>案例</strong>：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"># 聚合函数. 所有的<span class="keyword">null</span>不参与聚合计算</span><br><span class="line">#<span class="comment">---- 1. 统计该企业员工数量</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> emp ;</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(age) <span class="keyword">from</span> emp ;</span><br><span class="line">#<span class="comment">---- 2. 统计该企业员工的平均年龄</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(age) <span class="keyword">from</span> emp;</span><br><span class="line">#<span class="comment">---- 3. 统计该企业员工的最大年龄</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(age) <span class="keyword">from</span> emp;</span><br><span class="line">#<span class="comment">---- 4. 统计该企业员工的最小年龄</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">min</span>(age) <span class="keyword">from</span> emp;</span><br><span class="line">#<span class="comment">---- 5. 统计上海地区员工的年龄之和</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(age) <span class="keyword">from</span> emp <span class="keyword">where</span> workaddress<span class="operator">=</span><span class="string">&#x27;北京&#x27;</span>;</span><br></pre></td></tr></table></figure>

<h4 id="分组查询"><a href="#分组查询" class="headerlink" title="分组查询"></a>分组查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 基础语法</span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 [<span class="keyword">where</span> 条件] <span class="keyword">group</span> <span class="keyword">by</span> 分组字段名 [<span class="keyword">having</span> 分组后过滤条件];</span><br></pre></td></tr></table></figure>

<p><strong>where和having的区别</strong>：</p>
<ul>
<li>执行时机不同：where实对分组前进行过滤，不满足where的不参与分组；having是对分组后的结果进行筛选。</li>
<li>判断条件不同：where不能对聚合函数进行判断，而having可以</li>
</ul>
<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># 分组查询</span><br><span class="line"># <span class="number">1.</span> 根据性别分组，统计男性员工和女性员工的数量</span><br><span class="line"><span class="keyword">select</span> gender, <span class="built_in">count</span>(name) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> gender;</span><br><span class="line"># <span class="number">2.</span> 根据性别分组，统计男性员工和女性员工的平均年龄</span><br><span class="line"><span class="keyword">select</span> gender,<span class="built_in">avg</span>(age) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> gender;</span><br><span class="line"># <span class="number">3.</span> 查询年龄小于<span class="number">45</span>的员工，并根据工作地址分组，获取员工数量大于等于<span class="number">2</span>的工作地址</span><br><span class="line"><span class="keyword">select</span> workaddress, <span class="built_in">count</span>(<span class="operator">*</span>) address_count <span class="keyword">from</span> emp <span class="keyword">where</span> age<span class="operator">&lt;</span><span class="number">45</span> <span class="keyword">group</span> <span class="keyword">by</span> workaddress <span class="keyword">having</span> address_count<span class="operator">&gt;=</span><span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<h4 id="排序查询"><a href="#排序查询" class="headerlink" title="排序查询"></a>排序查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 基础语法</span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 [<span class="keyword">where</span> 条件] <span class="keyword">order</span> <span class="keyword">by</span> 字段<span class="number">1</span> 排序方式，字段<span class="number">2</span> 排序方式<span class="number">2</span>，...;</span><br></pre></td></tr></table></figure>

<p><strong>排序方式：</strong></p>
<ul>
<li>ASC: 升序（默认值，可以不写）。就是从小到大排列</li>
<li>DESC: 降序。从大到小排列</li>
</ul>
<p><strong>注意：</strong></p>
<p>如果是多字段排序，当第一个字段值相同时，才会对第二个字段进行排序。</p>
<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"># 排序查询</span><br><span class="line"># <span class="number">1.</span> 根据年龄对公司的员工进行升序排序</span><br><span class="line"><span class="keyword">select</span> name,age <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> age;</span><br><span class="line"># <span class="number">2.</span> 根据入职时间，对员工进行降序排序</span><br><span class="line"><span class="keyword">select</span> name, entrydate <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> entrydate <span class="keyword">desc</span>;</span><br><span class="line"># <span class="number">3.</span> 根据年龄对公司的员工进行升序排序，年龄相同按照入职时间进行降序排序</span><br><span class="line"><span class="keyword">select</span> name,age,entrydate <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> age, entrydate <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>

<h4 id="分页查询"><a href="#分页查询" class="headerlink" title="分页查询"></a>分页查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 基础语法</span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> emp limit 起始页码,查询数目;</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ol>
<li>起始页码从0开始，<strong>起始页码&#x3D;(查询页码-1) * 查询数目</strong></li>
<li>分页查询是数据库的方言，不同的数据库可能有不同的实现，MYSQL是LIMIT</li>
<li>如果查询的是第一个数据，可以省略起始页码，直接简写<strong>limit 查询数目</strong></li>
</ol>
<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"># 分页查询</span><br><span class="line"># <span class="number">1.</span>  查询第一页员工数据，每页展示<span class="number">10</span>条数据</span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp limit <span class="number">10</span>;</span><br><span class="line">#或者</span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp limit <span class="number">0</span>,<span class="number">10</span>;</span><br><span class="line"># <span class="number">2.</span>  查询第二页员工数据，每页展示<span class="number">10</span>条数据  起始索引<span class="operator">=</span> (目标页码<span class="number">-1</span>) <span class="operator">*</span> 每页的展示记录数目</span><br><span class="line"><span class="keyword">select</span> name,age <span class="keyword">from</span> emp limit <span class="number">10</span>,<span class="number">10</span>;</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h4 id="案例练习"><a href="#案例练习" class="headerlink" title="案例练习"></a>案例练习</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># 案例：</span><br><span class="line"># <span class="number">1.</span> 查询年龄为<span class="number">20</span>，<span class="number">21</span>，<span class="number">22</span>，<span class="number">23</span>岁的女性员工信息</span><br><span class="line"><span class="keyword">select</span> name,age,gender <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="keyword">in</span> (<span class="number">20</span>,<span class="number">21</span>,<span class="number">22</span>,<span class="number">23</span>) <span class="operator">&amp;&amp;</span> gender<span class="operator">=</span><span class="string">&#x27;女&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> name, age <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">20</span> <span class="keyword">or</span> age <span class="operator">=</span> <span class="number">21</span> <span class="operator">||</span> age <span class="operator">=</span> <span class="number">22</span> <span class="operator">||</span> age <span class="operator">=</span> <span class="number">23</span>;</span><br><span class="line"># <span class="number">2.</span> 查询性别为男，并且年龄在<span class="number">20</span><span class="number">-40</span>岁之间的三个字的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp;</span><br><span class="line"><span class="keyword">select</span> name <span class="string">&#x27;姓名&#x27;</span>,age <span class="string">&#x27;年龄&#x27;</span>,gender <span class="string">&#x27;性别&#x27;</span> <span class="keyword">from</span> emp <span class="keyword">where</span> gender<span class="operator">=</span><span class="string">&#x27;男&#x27;</span> <span class="keyword">and</span> age <span class="keyword">between</span> <span class="number">14</span> <span class="keyword">and</span> <span class="number">40</span> <span class="keyword">and</span> name <span class="keyword">like</span> <span class="string">&#x27;___&#x27;</span> <span class="keyword">order</span> <span class="keyword">by</span> age;</span><br><span class="line"># <span class="number">3.</span> 统计员工表中，年龄小于<span class="number">60</span>岁的，男性员工和女性员工的人数</span><br><span class="line"><span class="keyword">select</span> gender, <span class="built_in">count</span>(gender) <span class="keyword">from</span> emp <span class="keyword">where</span> age<span class="operator">&lt;</span><span class="number">60</span> <span class="keyword">group</span> <span class="keyword">by</span> gender;</span><br><span class="line"># <span class="number">4.</span> 查询所有年龄小于等于<span class="number">35</span>岁员工的姓名和年龄，并对查询结构按年龄升序，如果年龄相同按照入职时间降序</span><br><span class="line"><span class="keyword">select</span> name,age, entrydate <span class="keyword">from</span> emp <span class="keyword">where</span> age<span class="operator">&lt;=</span><span class="number">35</span> <span class="keyword">order</span> <span class="keyword">by</span> age , entrydate <span class="keyword">desc</span>;</span><br><span class="line"># <span class="number">5.</span> 查询性别为男，且年龄在<span class="number">20</span><span class="number">-40</span>岁之间以内的前五个员工信息，对查询的结果按照年龄升序，年龄同入职时间降序</span><br><span class="line"><span class="keyword">select</span> name, gender, age, entrydate <span class="keyword">from</span> emp  <span class="keyword">where</span> gender<span class="operator">=</span><span class="string">&#x27;男&#x27;</span> <span class="keyword">and</span> age <span class="keyword">between</span> <span class="number">20</span> <span class="keyword">and</span> <span class="number">40</span> <span class="keyword">order</span> <span class="keyword">by</span> age, entrydate <span class="keyword">desc</span> limit <span class="number">0</span>,<span class="number">5</span>;</span><br></pre></td></tr></table></figure>

<h3 id="DQL的执行顺序"><a href="#DQL的执行顺序" class="headerlink" title="DQL的执行顺序"></a>DQL的执行顺序</h3><p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220406184030493.png" alt="image-20220406184030493"></p>
<p><strong>注意：</strong></p>
<p>from &gt; where &gt; group by [having] &gt; select &gt; order by &gt; limit</p>
<h3 id="DCL"><a href="#DCL" class="headerlink" title="DCL"></a>DCL</h3><p><strong>数据控制语言</strong>：用来管理数据库的用户、控制数据库的访问权限</p>
<h4 id="用户管理"><a href="#用户管理" class="headerlink" title="用户管理"></a>用户管理</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"># DCL<span class="operator">-</span>管理用户</span><br><span class="line">#<span class="number">1.</span> 查询用户</span><br><span class="line">use mysql; # mysql的默认配置和其他的参数配置的基础数据库</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">2.</span> 创建用户</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span> identified <span class="keyword">by</span> <span class="string">&#x27;密码&#x27;</span>； # 主机名为地址名，表示指定地址的用户可以连接</span><br><span class="line"></span><br><span class="line">#<span class="number">3.</span> 修改用户密码</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span> identified <span class="keyword">with</span> mysql_native_password <span class="keyword">by</span> <span class="string">&#x27;新密码&#x27;</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">4.</span>  删除用户</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"># DCL ： 管理用户</span><br><span class="line">use mysql;</span><br><span class="line"># 查看当前用户</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span>;</span><br><span class="line"># 创建一个用户tom, 任何主机都能访问</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="string">&#x27;tom&#x27;</span>@<span class="string">&#x27;%&#x27;</span> identified <span class="keyword">by</span> <span class="string">&#x27;123456&#x27;</span>;</span><br><span class="line"># 修改密码</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">user</span> <span class="string">&#x27;tom&#x27;</span>@<span class="string">&#x27;%&#x27;</span> identified <span class="keyword">with</span> mysql_native_password <span class="keyword">by</span> <span class="string">&#x27;123&#x27;</span>;</span><br><span class="line"># 删除用户</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> <span class="string">&#x27;tom&#x27;</span>@<span class="string">&#x27;%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ul>
<li><code>&#39;用户名&#39;@&#39;%&#39;：表示所有主机地址的用户名都可以连接访问</code></li>
</ul>
<h4 id="权限管理"><a href="#权限管理" class="headerlink" title="权限管理"></a>权限管理</h4><p><strong>常见权限：</strong></p>
<table>
<thead>
<tr>
<th align="center">权限</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">ALL,ALL PRIVILEGES</td>
<td align="center">所有权限</td>
</tr>
<tr>
<td align="center">select</td>
<td align="center">查询数据</td>
</tr>
<tr>
<td align="center">delete</td>
<td align="center">删除数据</td>
</tr>
<tr>
<td align="center">update</td>
<td align="center">更新数据</td>
</tr>
<tr>
<td align="center">insert</td>
<td align="center">插入数据</td>
</tr>
<tr>
<td align="center">alter</td>
<td align="center">修改表</td>
</tr>
<tr>
<td align="center">drop</td>
<td align="center">修改数据库&#x2F;表&#x2F;视图</td>
</tr>
<tr>
<td align="center">create</td>
<td align="center">创建数据库&#x2F;表</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># <span class="number">1.</span> 查询权限</span><br><span class="line"><span class="keyword">show</span> grants <span class="keyword">for</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br><span class="line"></span><br><span class="line"># <span class="number">2.</span> 授予权限</span><br><span class="line"><span class="keyword">grant</span> 权限列表 <span class="keyword">on</span> 数据库.表名 <span class="keyword">to</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br><span class="line"># 注意：如果授予数据库下的所有表的权限，可以使用<span class="operator">*</span>号代表 <span class="operator">*</span>.<span class="operator">*</span> 表示所有数据库下的所有表</span><br><span class="line"></span><br><span class="line"># <span class="number">3.</span> 撤销权限</span><br><span class="line"><span class="keyword">revoke</span> 权限列表 <span class="keyword">on</span> 数据库.表名 <span class="keyword">from</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><strong>注意点：</strong></p>
<ol>
<li>多个权限之间，使用逗号分割</li>
<li>授权时，数据库名和表名可以使用<code>*</code>进行通配，代表所有</li>
</ol>
<p><strong>案例：</strong></p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"># 1. 查询权限</span><br><span class="line">show grants for &#x27;tom&#x27;@&#x27;%&#x27;</span><br><span class="line"># 显示USAGE，代表没有权限</span><br><span class="line"></span><br><span class="line"># 2. 给tom用户赋予数据库chales下的所有表的增加，删除，查询的权限</span><br><span class="line">grant delete,insert,select on chales.* to &#x27;tom&#x27;@&#x27;%&#x27;;</span><br><span class="line"></span><br><span class="line"># 3. 撤销tom用户的数据库chales下所有表的删除权限</span><br><span class="line">revoke delete on chales.* from &#x27;tom&#x27;@&#x27;%&#x27;;</span><br></pre></td></tr></table></figure>

<h2 id="函数"><a href="#函数" class="headerlink" title="函数"></a>函数</h2><p>  <strong>函数：</strong>是指一段可以直接被另一段程序调用的代码或程序</p>
<p>  <strong>分类：</strong></p>
<ul>
<li>字符串函数</li>
<li>数值函数</li>
<li>日期函数</li>
<li>流程函数</li>
</ul>
<h4 id="字符串函数"><a href="#字符串函数" class="headerlink" title="字符串函数"></a>字符串函数</h4><table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="center">concat(s1,s2,…,sn)</td>
<td align="center">字符串拼接，将s1,s2,…,sn拼接成一个字符串</td>
</tr>
<tr>
<td align="center">lower(str)</td>
<td align="center">将字符串str全部转为小写</td>
</tr>
<tr>
<td align="center">upper(str)</td>
<td align="center">将字符串str全部转为大写</td>
</tr>
<tr>
<td align="center">lpad(str,n,pad)</td>
<td align="center">左填充，将字符串pad对str的左边进行填充，填充后长度为n</td>
</tr>
<tr>
<td align="center">rpad(str,n,pad)</td>
<td align="center">右填充，将字符串pad对str的左边进行填充，填充后长度为n</td>
</tr>
<tr>
<td align="center">trim(str)</td>
<td align="center">去掉字符串头部和尾部的空格</td>
</tr>
<tr>
<td align="center">substring(str,start,len)</td>
<td align="center">返回字符串str从start开始的len长度的字符串(注意：start索引从1开始)</td>
</tr>
</tbody></table>
<p><strong>用法</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"># 函数</span><br><span class="line"><span class="comment">-- 字符串函数</span></span><br><span class="line"><span class="comment">-- concat</span></span><br><span class="line"><span class="keyword">select</span> concat(<span class="string">&#x27;Hello&#x27;</span>,<span class="string">&#x27; MySQL&#x27;</span>);</span><br><span class="line"><span class="comment">-- lower</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">lower</span>(<span class="string">&#x27;Hello&#x27;</span>);</span><br><span class="line"><span class="comment">-- upper</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">upper</span>(<span class="string">&#x27;Hello&#x27;</span>);</span><br><span class="line"><span class="comment">-- lpad</span></span><br><span class="line"><span class="keyword">select</span> lpad(<span class="string">&#x27;01&#x27;</span>, <span class="number">5</span>, <span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"><span class="comment">-- rpad</span></span><br><span class="line"><span class="keyword">select</span> rpad(<span class="string">&#x27;01&#x27;</span>, <span class="number">5</span>, <span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"><span class="comment">-- trim</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">trim</span>(<span class="string">&#x27; Hello  Mysql &#x27;</span>);</span><br><span class="line"><span class="comment">-- substring</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">substring</span>(<span class="string">&#x27;Hello MySQL&#x27;</span>, <span class="number">1</span>, <span class="number">5</span>);</span><br></pre></td></tr></table></figure>

<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 企业员工的工号，统一为<span class="number">5</span>位数，不足<span class="number">5</span>位数的全部在前面补<span class="number">0</span>。比如：<span class="number">1</span>号员工的工号应该为<span class="number">00001</span></span><br><span class="line"><span class="keyword">select</span> name, lpad(workno, <span class="number">5</span> ,<span class="string">&#x27;0&#x27;</span>) <span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<h4 id="数值函数"><a href="#数值函数" class="headerlink" title="数值函数"></a>数值函数</h4><table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="center">ceil(x)</td>
<td align="center">向上取整</td>
</tr>
<tr>
<td align="center">floor(x)</td>
<td align="center">向下取整</td>
</tr>
<tr>
<td align="center">mod(x,y)</td>
<td align="center">返回x&#x2F;y的模</td>
</tr>
<tr>
<td align="center">rand()</td>
<td align="center">返回0~1内的随机数</td>
</tr>
<tr>
<td align="center">round(x,y)</td>
<td align="center">求参数x的四舍五入值，保留y位小数</td>
</tr>
</tbody></table>
<p><strong>使用：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 数值函数</span></span><br><span class="line"><span class="comment">-- ceil</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">ceil</span>(<span class="number">1.1</span>); # 向上取整，只要小数有值，就向上取整</span><br><span class="line"><span class="comment">-- floor</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">floor</span>(<span class="number">1.9</span>); # 向下取整，不管小数位，直接要整数位</span><br><span class="line"><span class="comment">-- mod</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">mod</span>(<span class="number">4</span>,<span class="number">6</span>);</span><br><span class="line"><span class="comment">-- rand</span></span><br><span class="line"><span class="keyword">select</span> rand(); # <span class="number">0</span><span class="operator">~</span><span class="number">1</span>的范围内取值</span><br><span class="line"><span class="comment">-- round</span></span><br><span class="line"><span class="keyword">select</span> round(<span class="number">7.3355</span>, <span class="number">2</span>); # 四舍五入</span><br></pre></td></tr></table></figure>

<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"># 案例：通过数据库的函数，生成一个六位数的随机验证码</span><br><span class="line"><span class="keyword">select</span> lpad(round(rand()<span class="operator">*</span><span class="number">1000000</span>, <span class="number">0</span>),<span class="number">6</span>,<span class="string">&#x27;0&#x27;</span>);</span><br><span class="line"><span class="comment">-- 中间涉及了数值向字符串之间的转换</span></span><br><span class="line"><span class="comment">-- round生成0-1之间的随机数，*1000000 将其扩展成5-6位整数</span></span><br><span class="line"><span class="comment">-- 可能生成的位数不满足6位，原因是：0.01之类的数</span></span><br><span class="line"><span class="comment">-- lpad就是为了满足整个要求</span></span><br></pre></td></tr></table></figure>

<h4 id="日期函数"><a href="#日期函数" class="headerlink" title="日期函数"></a>日期函数</h4><table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="center">curdate()</td>
<td align="center">返回当前日期</td>
</tr>
<tr>
<td align="center">curtime()</td>
<td align="center">返回当前时间</td>
</tr>
<tr>
<td align="center">now()</td>
<td align="center">返回当前时间和日期</td>
</tr>
<tr>
<td align="center">year(date)</td>
<td align="center">获取指定date的年份</td>
</tr>
<tr>
<td align="center">month(date)</td>
<td align="center">获取指定date的月份</td>
</tr>
<tr>
<td align="center">day(date)</td>
<td align="center">获取指定date的日期</td>
</tr>
<tr>
<td align="center">date_add(date, Interval expr type)</td>
<td align="center">返回一个日期&#x2F;时间值加上一个时间间隔expr后的时间值</td>
</tr>
<tr>
<td align="center">datediff(date1,date2)</td>
<td align="center">返回date1到date2之间的天数。一般date1的日期晚于date2否则会出现负数</td>
</tr>
</tbody></table>
<p><strong>使用：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 日期函数</span></span><br><span class="line"><span class="comment">-- curdate()</span></span><br><span class="line"><span class="keyword">select</span> curdate();</span><br><span class="line"><span class="comment">-- curtime()</span></span><br><span class="line"><span class="keyword">select</span> curtime();</span><br><span class="line"><span class="comment">-- now()</span></span><br><span class="line"><span class="keyword">select</span> now();</span><br><span class="line"><span class="comment">-- year</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">year</span>(now());</span><br><span class="line"><span class="comment">-- month</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">month</span>(now());</span><br><span class="line"><span class="comment">-- day</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">day</span>(now());</span><br><span class="line"><span class="comment">-- date_add</span></span><br><span class="line"><span class="keyword">select</span> date_add(now(),<span class="type">interval</span> <span class="number">70</span> <span class="keyword">year</span>);</span><br><span class="line"><span class="comment">-- datediff</span></span><br><span class="line"><span class="keyword">select</span> datediff(<span class="string">&#x27;2022-10-11&#x27;</span>,<span class="string">&#x27;2021-10-11&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询所有员工的入职天数，并根据入职天数倒序排序</span></span><br><span class="line"><span class="keyword">select</span> name,datediff(now(),entrydate) <span class="keyword">as</span> <span class="string">&#x27;入职天数&#x27;</span> <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> 入职天数 <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>

<h4 id="流程函数"><a href="#流程函数" class="headerlink" title="流程函数"></a>流程函数</h4><table>
<thead>
<tr>
<th align="center">函数</th>
<th align="center">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="center">if(value,t,f)</td>
<td align="center">如果value为true，返回t,否则返回f</td>
</tr>
<tr>
<td align="center">ifnull(value1,value2)</td>
<td align="center">如果value1不为空，返回value1，否则返回value2</td>
</tr>
<tr>
<td align="center">case when [val1] then [res] .. else [default] end</td>
<td align="center">如果val1为true返回res1，..否则返回default默认值</td>
</tr>
<tr>
<td align="center">case [expr] when [val1] then [res1]… else [default] end</td>
<td align="center">如果expr的值等于val1，返回res1，..否则返回default的值</td>
</tr>
</tbody></table>
<p><strong>使用：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 流程函数</span></span><br><span class="line"><span class="comment">-- if</span></span><br><span class="line"><span class="keyword">select</span> if(<span class="literal">true</span>,<span class="string">&#x27;OK&#x27;</span>,<span class="string">&#x27;ERROR&#x27;</span>); <span class="comment">-- 返回ok</span></span><br><span class="line"><span class="keyword">select</span> if(<span class="literal">false</span>,<span class="string">&#x27;OK&#x27;</span>,<span class="string">&#x27;ERROR&#x27;</span>); <span class="comment">-- 返回error</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- ifnull</span></span><br><span class="line"><span class="keyword">select</span> ifnull(<span class="string">&#x27;ok&#x27;</span>,<span class="string">&#x27;error&#x27;</span>); <span class="comment">-- 返回ok</span></span><br><span class="line"><span class="keyword">select</span> ifnull(<span class="keyword">null</span>,<span class="keyword">null</span>); <span class="comment">-- 返回null</span></span><br><span class="line"><span class="keyword">select</span> ifnull(<span class="keyword">null</span>,<span class="string">&#x27;error&#x27;</span>); <span class="comment">-- 返回 error</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- case when</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">case</span> <span class="keyword">when</span> <span class="number">10</span><span class="operator">&gt;</span><span class="number">5</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span>;</span><br><span class="line"><span class="comment">-- case expr when</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">case</span> <span class="string">&#x27;开发&#x27;</span> <span class="keyword">when</span> <span class="string">&#x27;测试&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;成功&#x27;</span> <span class="keyword">when</span> <span class="string">&#x27;开发&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;失败&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;重新输入&#x27;</span> <span class="keyword">end</span>;</span><br></pre></td></tr></table></figure>

<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"># 需求<span class="number">1</span>：查询emp员工姓名和工作地址(北京<span class="operator">/</span>上海<span class="comment">---》国内，美国和其他---》国外)</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">       name,</span><br><span class="line">       <span class="keyword">case</span> workaddress <span class="keyword">when</span> <span class="string">&#x27;北京&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;国内&#x27;</span> <span class="keyword">when</span> <span class="string">&#x27;河北&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;国内&#x27;</span> <span class="keyword">when</span> <span class="string">&#x27;上海&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;国内&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;国外&#x27;</span> <span class="keyword">end</span></span><br><span class="line"><span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line"># 需求<span class="number">2</span>：统计班级各个学员的成绩，展示规则如下：</span><br><span class="line"><span class="comment">-- &gt;=85 优秀</span></span><br><span class="line"><span class="comment">-- &gt;=60 及格</span></span><br><span class="line"><span class="comment">-- 否则，不及格</span></span><br><span class="line"></span><br><span class="line"><span class="comment">-- 建表语句和插入数据</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> score</span><br><span class="line">(</span><br><span class="line">    id      <span class="type">int</span> comment <span class="string">&#x27;学号&#x27;</span>,</span><br><span class="line">    name    <span class="type">varchar</span>(<span class="number">10</span>) comment <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">    english tinyint unsigned comment <span class="string">&#x27;英语成绩&#x27;</span>,</span><br><span class="line">    math    tinyint unsigned comment <span class="string">&#x27;数学成绩&#x27;</span>,</span><br><span class="line">    chinese tinyint unsigned comment <span class="string">&#x27;语文成绩&#x27;</span></span><br><span class="line">) comment <span class="string">&#x27;成绩表&#x27;</span>;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> score <span class="keyword">values</span> (<span class="number">1</span>, <span class="string">&#x27;张三&#x27;</span>,<span class="number">55</span>,<span class="number">75</span>,<span class="number">86</span>),(<span class="number">2</span>, <span class="string">&#x27;李斯&#x27;</span>,<span class="number">88</span>,<span class="number">90</span>,<span class="number">97</span>),(<span class="number">3</span>, <span class="string">&#x27;王五&#x27;</span>,<span class="number">77</span>,<span class="number">23</span>,<span class="number">80</span>),(<span class="number">4</span>, <span class="string">&#x27;赵飞&#x27;</span>,<span class="number">99</span>,<span class="number">99</span>,<span class="number">99</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 实现要求</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">       name,</span><br><span class="line">       <span class="keyword">case</span> <span class="keyword">when</span> math<span class="operator">&gt;=</span><span class="number">85</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">when</span> math<span class="operator">&gt;=</span><span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span> <span class="keyword">as</span> <span class="string">&#x27;数学成绩&#x27;</span>,</span><br><span class="line">       <span class="keyword">case</span> <span class="keyword">when</span> english<span class="operator">&gt;=</span><span class="number">85</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">when</span> english<span class="operator">&gt;=</span><span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span> <span class="keyword">as</span> <span class="string">&#x27;英语成绩&#x27;</span>,</span><br><span class="line">       <span class="keyword">case</span> <span class="keyword">when</span> chinese<span class="operator">&gt;=</span><span class="number">85</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">when</span> chinese<span class="operator">&gt;=</span><span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span> <span class="keyword">as</span> <span class="string">&#x27;语文成绩&#x27;</span></span><br><span class="line"><span class="keyword">from</span> score;</span><br></pre></td></tr></table></figure>

<h2 id="约束"><a href="#约束" class="headerlink" title="约束"></a>约束</h2><h4 id="概述"><a href="#概述" class="headerlink" title="概述"></a>概述</h4><ol>
<li><p>概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据</p>
</li>
<li><p>目的：保证数据库中数据的正确、有效性和完整性</p>
</li>
<li><p>分类</p>
<table>
<thead>
<tr>
<th align="center">约束</th>
<th align="center">描述</th>
<th align="center">关键字</th>
</tr>
</thead>
<tbody><tr>
<td align="center">非空约束</td>
<td align="center">限制该字段不能为null</td>
<td align="center">NOT NULL</td>
</tr>
<tr>
<td align="center">唯一约束</td>
<td align="center">限制字段内的所有数据都是唯一的，不重复</td>
<td align="center">UNIQUE</td>
</tr>
<tr>
<td align="center">主键约束</td>
<td align="center">主键是一行数据的唯一标识，要求非空且唯一</td>
<td align="center">primary key</td>
</tr>
<tr>
<td align="center">默认约束</td>
<td align="center">保存数据时，如果未指定该字段的值，则采用默认值</td>
<td align="center">default</td>
</tr>
<tr>
<td align="center">检查约束(8.0以后)</td>
<td align="center">保证字段值满足某一条件</td>
<td align="center">check</td>
</tr>
<tr>
<td align="center">外键约束</td>
<td align="center">用来让两张表数据之间建立连接，保证数据的一致性和完整性</td>
<td align="center">foreign key</td>
</tr>
</tbody></table>
</li>
</ol>
<h4 id="约束演示"><a href="#约束演示" class="headerlink" title="约束演示"></a>约束演示</h4><p>  <img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220407160241441.png" alt="image-20220407160241441"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 约束的使用演示</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> <span class="keyword">user</span>(</span><br><span class="line">    id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment comment <span class="string">&#x27;主键&#x27;</span>,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="keyword">null</span> <span class="keyword">unique</span> comment <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">    age tinyint unsigned <span class="keyword">check</span> ( age<span class="operator">&gt;</span><span class="number">0</span> <span class="keyword">and</span> age<span class="operator">&lt;=</span><span class="number">120</span> ) comment <span class="string">&#x27;年龄&#x27;</span>,</span><br><span class="line">    status <span class="type">char</span>(<span class="number">1</span>) <span class="keyword">default</span> <span class="string">&#x27;1&#x27;</span> comment <span class="string">&#x27;状态&#x27;</span>,</span><br><span class="line">    gender <span class="type">char</span>(<span class="number">1</span>) comment <span class="string">&#x27;性别&#x27;</span></span><br><span class="line">)comment <span class="string">&#x27;用户表&#x27;</span>;</span><br><span class="line"><span class="comment">-- 插入数据</span></span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(name,age,status,gender) <span class="keyword">values</span> (<span class="string">&#x27;zhangsan&#x27;</span>,<span class="number">120</span>,<span class="string">&#x27;0&#x27;</span>,<span class="string">&#x27;男&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> <span class="keyword">user</span>(name,age,gender) <span class="keyword">values</span> (<span class="string">&#x27;李斯&#x27;</span>,<span class="number">55</span>,<span class="string">&#x27;女&#x27;</span>);</span><br></pre></td></tr></table></figure>

<h4 id="外键约束"><a href="#外键约束" class="headerlink" title="外键约束"></a>外键约束</h4><p><strong>概念：</strong>外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性</p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220407161042671.png" alt="image-20220407161042671"></p>
<p><strong>注意：</strong></p>
<ul>
<li>有外键的表称为子表，也称为从表</li>
<li>外键所对应的主键表称为父表，也称为主表</li>
</ul>
<p><strong>语法：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"># 添加外键，创建表时</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">	字段 数据类型,</span><br><span class="line">    ...</span><br><span class="line">    [<span class="keyword">constraint</span>] [外键名称] <span class="keyword">foreign</span> key (外键字段名) <span class="keyword">references</span> 主表(主表列名)</span><br><span class="line">)</span><br><span class="line"># 添加外键，创建完成表之后</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">constraint</span> 外键名称 <span class="keyword">foreign</span> key （外键字段名） <span class="keyword">references</span> 主表(主表列名)</span><br><span class="line"></span><br><span class="line"># 删除外键</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">drop</span> <span class="keyword">foreign</span> key 外键名称;</span><br></pre></td></tr></table></figure>

<p><strong>案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 外键约束的使用</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> dept(</span><br><span class="line">    id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>) <span class="keyword">not</span> <span class="keyword">null</span></span><br><span class="line">)comment <span class="string">&#x27;部门表&#x27;</span>;</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> dept(name) <span class="keyword">values</span> (<span class="string">&#x27;研发部&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> dept(name) <span class="keyword">values</span> (<span class="string">&#x27;市场部&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> dept(name) <span class="keyword">values</span> (<span class="string">&#x27;财务部&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> dept(name) <span class="keyword">values</span> (<span class="string">&#x27;销售部&#x27;</span>);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> dept(name) <span class="keyword">values</span> (<span class="string">&#x27;总经办&#x27;</span>);</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> emp <span class="keyword">add</span> <span class="keyword">column</span> dept_id <span class="type">int</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">1</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">1</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">2</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">2</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">3</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">3</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">4</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">4</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">1</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">5</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">1</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">6</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">1</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">7</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">1</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">8</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">2</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">9</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">3</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">10</span>;</span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> dept_id <span class="operator">=</span> <span class="number">2</span> <span class="keyword">where</span> id <span class="operator">=</span> <span class="number">11</span>;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 设置外键</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> emp <span class="keyword">add</span> <span class="keyword">constraint</span> emp_dept_foreign_key <span class="keyword">foreign</span> key emp(dept_id) <span class="keyword">references</span> dept(id);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 删除外键</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> emp <span class="keyword">drop</span> <span class="keyword">foreign</span> key emp_dept_foreign_key;</span><br></pre></td></tr></table></figure>

<p><strong>设置外键约束规则</strong></p>
<p><code>删除/更新行为</code></p>
<table>
<thead>
<tr>
<th align="center">行为</th>
<th align="center">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="center">NO ACTION</td>
<td align="center">当父表中删除&#x2F;更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除&#x2F;更新(与RESTRICT一致)</td>
</tr>
<tr>
<td align="center">RESTRICT</td>
<td align="center">当父表中删除&#x2F;更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除&#x2F;更新(与NO ACTION一致)</td>
</tr>
<tr>
<td align="center">CASCADE</td>
<td align="center">当父表中删除&#x2F;更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除&#x2F;更新外键在子表中的记录</td>
</tr>
<tr>
<td align="center">SET NULL</td>
<td align="center">当父表中删除&#x2F;更新对应记录时，首先检查该记录是否有对应外键，如果有，则设置子表中该外键值为null（这就要求该外键允许取值为null）</td>
</tr>
<tr>
<td align="center">SET DEFAULT</td>
<td align="center">父表有变更时，子表将外键列设置成一个默认的值(innodb不支持)</td>
</tr>
</tbody></table>
<p><strong>语法</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">constraint</span> 外键名称 <span class="keyword">foreign</span> key 外键字段 <span class="keyword">references</span> 主表名(主表字段) <span class="keyword">on</span> <span class="keyword">update</span> cascade <span class="keyword">on</span> <span class="keyword">delete</span> cascade;</span><br></pre></td></tr></table></figure>

<p><strong>使用</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 设置外键，并将更新设置为cascade 删除设置为<span class="keyword">set</span> <span class="keyword">null</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> emp <span class="keyword">add</span> <span class="keyword">constraint</span> emp_dept_foreign_key <span class="keyword">foreign</span> key emp(dept_id) <span class="keyword">references</span> dept(id) <span class="keyword">on</span> <span class="keyword">update</span> cascade <span class="keyword">on</span> <span class="keyword">delete</span> <span class="keyword">set</span> <span class="keyword">null</span>;</span><br></pre></td></tr></table></figure>

<h2 id="多表查询"><a href="#多表查询" class="headerlink" title="多表查询"></a>多表查询</h2><p>目录：</p>
<ul>
<li>多表关系</li>
<li>多表查询概述</li>
<li>内连接</li>
<li>外连接</li>
<li>子连接</li>
<li>子查询</li>
<li>多表查询案例</li>
</ul>
<h4 id="多表关系"><a href="#多表关系" class="headerlink" title="多表关系"></a>多表关系</h4><p><strong>概述：</strong>项目开发中，再进行数据表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本分为三种：</p>
<ul>
<li>一对多(多对一)<ul>
<li>员工和部门的关系。一个员工对应多个部门</li>
</ul>
</li>
<li>多对多<ul>
<li>学生和课程关系：一个学生可以选择多个课程，一个课程可以被多个学生选择。</li>
</ul>
</li>
<li>一对一<ul>
<li>学生和学生详情信息：学生有基本信息，但是如果查询列太多就会查询不方便，所以将信息分割成基础信息和详情信息。</li>
</ul>
</li>
</ul>
<p><strong>案例：</strong></p>
<ol>
<li><p>一对多(多对一)</p>
<ul>
<li>案例：部门与员工的关系</li>
<li>关系：一个部门对应多个员工，一个员工对应多个部门</li>
<li>实现：在多的一方建立外键，指向一的一方的外键</li>
</ul>
<p><img src="C:\Users\84350\Desktop\image-20220411095334891.png" alt="image-20220411095334891"></p>
</li>
<li><p>多对多</p>
<ul>
<li><p>案例：学生与课程的关系</p>
</li>
<li><p>关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择</p>
</li>
<li><p>实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键</p>
</li>
</ul>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220411095623612.png" alt="image-20220411095623612"></p>
</li>
<li><p>一对一</p>
<ul>
<li>案例：用户与用户详情的关系</li>
<li>关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提高操作效率</li>
<li>实现：在任意一方加入外键，关联另外一方的主键，并设置外键为唯一的(UNIQUE)</li>
</ul>
<p><img src="C:\Users\84350\Desktop\image-20220411102048027.png" alt="image-20220411102048027"></p>
</li>
</ol>
<h4 id="多表查询概述"><a href="#多表查询概述" class="headerlink" title="多表查询概述"></a>多表查询概述</h4><ul>
<li><p>概述：指从多张表中查询数据</p>
</li>
<li><p>笛卡尔积：笛卡尔积是指在数学中，两个集合A集合和B集合所有组合的情况。</p>
</li>
<li><p>目标：多表查询时，需要消除无效的笛卡尔积</p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220411163909120.png" alt="image-20220411163909120"></p>
</li>
<li><p>多表查询的分别</p>
<ol>
<li><p>连接查询</p>
<ul>
<li>内连接：相当于查询A、B交集部分数据</li>
<li>外连接：<ul>
<li>左外连接：查询左表所有数据，以及两张表交集部分数据</li>
<li>右外连接：查询右表所有数据，以及两张表交集部分数据</li>
</ul>
</li>
<li>自连接：当前表与自身的连接查询，自连接必须使用表别名</li>
</ul>
</li>
<li><p>子查询</p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220411164249203.png" alt="image-20220411164249203"></p>
</li>
</ol>
</li>
</ul>
<h4 id="内连接"><a href="#内连接" class="headerlink" title="内连接"></a>内连接</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 隐式内连接</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span>，表<span class="number">2</span> <span class="keyword">where</span> 条件;</span><br><span class="line"><span class="comment">-- 显示内连接</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 连接条件; </span><br></pre></td></tr></table></figure>

<p><strong>内连接就是查询两张表交集的部分：AB的交集</strong></p>
<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 内连接演示</span></span><br><span class="line"><span class="comment">-- 1. 查询每一个员工的姓名，以及关联的部门名称（隐式）</span></span><br><span class="line"><span class="keyword">select</span> emp.name, dept.name <span class="keyword">from</span> emp,dept <span class="keyword">where</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br><span class="line"><span class="comment">-- 2. 查询每一个员工的姓名，以及关联的部门名称（显示）</span></span><br><span class="line"><span class="keyword">select</span> emp.name, dept.name <span class="keyword">from</span> emp <span class="keyword">join</span> dept <span class="keyword">on</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br></pre></td></tr></table></figure>

<h4 id="外连接"><a href="#外连接" class="headerlink" title="外连接"></a>外连接</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 左外连接：相当于查询表1(左表)的所有数据包含表1和表2的交集部分的数据</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">left</span>[<span class="keyword">outer</span>] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件...;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 右外连接：相当于查询表2(右表)的所有数据包含表1和表2的交集部分的数据</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">right</span> [<span class="keyword">outer</span>] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件...;</span><br></pre></td></tr></table></figure>

<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. 查询emp的所有数据，和对应的部门信息(左外连接)</span></span><br><span class="line"><span class="keyword">select</span> emp.<span class="operator">*</span>, dept.name <span class="keyword">from</span> emp <span class="keyword">left</span> <span class="keyword">join</span> dept <span class="keyword">on</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br><span class="line"><span class="comment">-- 2. 查询emp的所有数据，和对应的部门信息(右外连接)</span></span><br><span class="line"><span class="keyword">select</span> emp.<span class="operator">*</span>, dept.name <span class="keyword">from</span> emp <span class="keyword">right</span> <span class="keyword">join</span> dept <span class="keyword">on</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br><span class="line"><span class="comment">-- 3. 查询emp的所有数据，和对应的部门信息(右连接换为左连接)</span></span><br><span class="line"><span class="keyword">select</span> emp.<span class="operator">*</span>, dept.name <span class="keyword">from</span> dept <span class="keyword">left</span> <span class="keyword">join</span> emp <span class="keyword">on</span> dept.id <span class="operator">=</span> emp.dept_id;</span><br></pre></td></tr></table></figure>

<p><strong>注意点：</strong></p>
<ol>
<li>左外连接和右外连接没有什么区别，一般使用左外连接，转换成右外连接只需将表1换为表2即可；</li>
<li>左外注意是左表的全部：含有左表中没有对应右表的数据</li>
<li>右外注意是右表的全部：同理</li>
</ol>
<h4 id="自连接"><a href="#自连接" class="headerlink" title="自连接"></a>自连接</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator">-</span> 自连接查询，可以是内连接查询，可以是外连接查询</span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> 别名A <span class="keyword">join</span> 表<span class="number">1</span> 别名B <span class="keyword">on</span> 条件...;</span><br><span class="line"><span class="comment">-- 注意： 必须取别名</span></span><br></pre></td></tr></table></figure>

<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. 查询员工以及所属领导的名称 &lt;内连接，两种写法，隐式/显式&gt;</span></span><br><span class="line"><span class="keyword">select</span> emp.name <span class="string">&#x27;员工名称&#x27;</span>, leader.name <span class="string">&#x27;领导名称&#x27;</span> <span class="keyword">from</span> employee emp <span class="keyword">join</span> employee leader <span class="keyword">on</span> emp.leaderid <span class="operator">=</span> leader.id <span class="keyword">order</span> <span class="keyword">by</span> 领导名称 <span class="keyword">desc</span>;</span><br><span class="line"><span class="keyword">select</span> emp.name <span class="string">&#x27;员工名称&#x27;</span>, leader.name <span class="string">&#x27;领导名称&#x27;</span> <span class="keyword">from</span> employee emp, employee leader <span class="keyword">where</span> emp.leaderid <span class="operator">=</span> leader.id <span class="keyword">order</span> <span class="keyword">by</span> 领导名称 <span class="keyword">desc</span>;</span><br><span class="line"><span class="comment">-- 2. 查询所有员工emp及其领导名字emp ，如果员工没有领导也要查询出来</span></span><br><span class="line"><span class="keyword">select</span> emp.name <span class="string">&#x27;员工名称&#x27;</span>, leader.name <span class="string">&#x27;领导名称&#x27;</span> <span class="keyword">from</span> employee emp <span class="keyword">left</span> <span class="keyword">join</span> employee leader <span class="keyword">on</span> emp.leaderid <span class="operator">=</span> leader.id <span class="keyword">order</span> <span class="keyword">by</span> 领导名称 <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>

<h4 id="联合查询"><a href="#联合查询" class="headerlink" title="联合查询"></a>联合查询</h4><p><strong>对于union&#x2F;union all查询，就是把多次查询的结果合并起来，形成一个新的查询结果集</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表A </span><br><span class="line"><span class="keyword">union</span> [<span class="keyword">all</span>]</span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表B；</span><br></pre></td></tr></table></figure>

<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. 查询薪资6000.0的员工(不含6000.0)，且年龄大于50岁的员工</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> employee <span class="keyword">where</span> salary<span class="operator">&lt;</span><span class="number">6000.0</span> <span class="keyword">union</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> employee <span class="keyword">where</span> age <span class="operator">&gt;</span> <span class="number">50</span>;</span><br></pre></td></tr></table></figure>

<p><strong>注意：</strong></p>
<ul>
<li>union&#x2F;union all是将<strong>两个查询的结果用<code>逻辑或</code>聚合起来</strong></li>
<li>union会将结果去重，union all 不会将去重</li>
</ul>
<h4 id="子查询"><a href="#子查询" class="headerlink" title="子查询"></a>子查询</h4><ul>
<li><p>概念：SQL语句中嵌套select语句，称为<strong>嵌套查询</strong>，<strong>又称子查询</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t1 <span class="keyword">where</span> <span class="keyword">column</span> <span class="operator">=</span> (<span class="keyword">select</span> column1 <span class="keyword">from</span> t2)</span><br></pre></td></tr></table></figure>

<p><strong>子查询外部的语句可以是增删查改的任何一个</strong></p>
</li>
<li><p>根据子查询的结果不同，分为：</p>
<ul>
<li>标量子查询(子查询结果为单个值)</li>
<li>列子查询(子查询结果为一列)</li>
<li>行子查询(子查询结果为一行)</li>
<li>表子查询(子查询的结果为多行多列)</li>
</ul>
</li>
<li><p>根据子查询位置，分为where之后、from之后、select之后</p>
</li>
</ul>
<h5 id="标量子查询"><a href="#标量子查询" class="headerlink" title="标量子查询"></a>标量子查询</h5><p><strong>概念：</strong>子查询返回的结果是单个值(数字，字符串，日期等)，最简单的形式，这种子查询称为标量子查询</p>
<p><strong>常用操作符：</strong>&#x3D;、&lt;&gt;、&gt;、&gt;&#x3D;、&lt;、&lt;&#x3D;</p>
<p><strong>注意：</strong>两个SQL语句，被嵌套的那个SQL语句要放到<code>()</code>内</p>
<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. 查询“研发部”的所有员工信息</span></span><br><span class="line"><span class="comment">-- 1.1 查询研发部的id</span></span><br><span class="line"><span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>;</span><br><span class="line"><span class="comment">-- 1.2 查询根据id查询所有的员工信息</span></span><br><span class="line"><span class="keyword">select</span> name <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 2. 查询在“赵敏”入职之后的员工信息</span></span><br><span class="line"><span class="comment">-- 2.1 查询赵敏的入职日期</span></span><br><span class="line"><span class="keyword">select</span> entrydate <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;赵敏&#x27;</span>;</span><br><span class="line"><span class="comment">-- 2.2 查询赵敏入职日期之后的员工信息</span></span><br><span class="line"><span class="keyword">select</span> name, entrydate <span class="keyword">from</span> emp <span class="keyword">where</span> entrydate<span class="operator">&gt;</span> (<span class="keyword">select</span> entrydate <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;赵敏&#x27;</span>) <span class="keyword">order</span> <span class="keyword">by</span> entrydate;</span><br></pre></td></tr></table></figure>

<h5 id="列子查询"><a href="#列子查询" class="headerlink" title="列子查询"></a>列子查询</h5><p><strong>概念：</strong>子查询返回的结果是一列(或者说是多行)，这种子查询称为列子查询</p>
<p><strong>操作符：</strong>IN、NOT IN、ANY、SOME、ALL</p>
<table>
<thead>
<tr>
<th align="center">操作符</th>
<th align="center">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="center">IN</td>
<td align="center">在指定的集合范围之内，多选一</td>
</tr>
<tr>
<td align="center">NOT IN</td>
<td align="center">不在指定的集合范围之内</td>
</tr>
<tr>
<td align="center">ANY</td>
<td align="center">子查询返回列表中，有任意一个满足即可</td>
</tr>
<tr>
<td align="center">SOME</td>
<td align="center">与ANY等同，使用SOME的地方都可以使用ANY</td>
</tr>
<tr>
<td align="center">ALL</td>
<td align="center">子查询返回列表的所有值都必须满足</td>
</tr>
</tbody></table>
<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1. 查询“研发部”和“市场部”的所有员工信息</span></span><br><span class="line"><span class="keyword">select</span> dept.id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;市场部&#x27;</span> <span class="keyword">or</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> emp.name <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="keyword">in</span> (<span class="keyword">select</span> dept.id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;市场部&#x27;</span> <span class="keyword">or</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 2. 查询比财务部所有员工工资都高的员工信息</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;财务部&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;财务部&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> emp.name, emp.salary <span class="keyword">from</span> emp <span class="keyword">where</span> salary <span class="operator">&gt;</span> <span class="keyword">all</span>(<span class="keyword">select</span> salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;财务部&#x27;</span>));</span><br><span class="line"><span class="comment">-- 3. 查询比研发部其中任意一人的工资都高的员工信息</span></span><br><span class="line"><span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> name, salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>);</span><br><span class="line"><span class="comment">-- 3.* 含研发部</span></span><br><span class="line"><span class="keyword">select</span> name, salary <span class="keyword">from</span> emp <span class="keyword">where</span> salary <span class="operator">&gt;</span> <span class="keyword">any</span>(<span class="keyword">select</span> salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>));</span><br><span class="line"><span class="comment">-- 3.* 不含研发部</span></span><br><span class="line"><span class="keyword">select</span> emp1.name, emp1.salary, emp1.dept_id <span class="keyword">from</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">&lt;&gt;</span> <span class="number">1</span>) <span class="keyword">as</span> emp1 <span class="keyword">where</span> salary <span class="operator">&gt;</span> <span class="keyword">any</span>(<span class="keyword">select</span> salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;研发部&#x27;</span>));</span><br></pre></td></tr></table></figure>

<h5 id="行子查询"><a href="#行子查询" class="headerlink" title="行子查询"></a>行子查询</h5><p><strong>概念：</strong>子查询的返回结果是一行(或者多列)，这种查询称为行子查询</p>
<p><strong>常用操作符：</strong>&#x3D;、	&lt;&gt;、IN、NOT IN</p>
<p><strong>案例：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 行子查询</span></span><br><span class="line"><span class="comment">-- 1. 查询与“张无忌”的薪资及地区相同的员工信息</span></span><br><span class="line"><span class="keyword">select</span> salary, workaddress <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张无忌&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> name, salary, workaddress <span class="keyword">from</span> emp <span class="keyword">where</span> (salary, workaddress) <span class="operator">=</span> (<span class="keyword">select</span> salary, workaddress <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张无忌&#x27;</span>);</span><br><span class="line"></span><br></pre></td></tr></table></figure>

<h5 id="表子查询"><a href="#表子查询" class="headerlink" title="表子查询"></a>表子查询</h5><p><strong>概念：</strong>子查询返回的结果是多行多列，这种子查询称为<code>表子查询</code></p>
<p><strong>常用操作符：</strong>IN</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 表子查询</span></span><br><span class="line"><span class="comment">-- 1. 查询与“张无忌”,&quot;小昭&quot;的薪资和工作地点相同的员工信息</span></span><br><span class="line"><span class="keyword">select</span> salary,workaddress <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张无忌&#x27;</span> <span class="keyword">or</span> name <span class="operator">=</span> <span class="string">&#x27;小昭&#x27;</span>;</span><br><span class="line"><span class="keyword">select</span> name,salary, workaddress <span class="keyword">from</span> emp <span class="keyword">where</span> (salary, workaddress) <span class="keyword">in</span> (<span class="keyword">select</span> salary,workaddress <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张无忌&#x27;</span> <span class="keyword">or</span> name <span class="operator">=</span> <span class="string">&#x27;小昭&#x27;</span>);</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 2. 查询入职日期是“2000-01-05”之后的员工信息和部门信息</span></span><br><span class="line"><span class="keyword">select</span> e.name,e.entrydate,d.name <span class="keyword">from</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> entrydate<span class="operator">&gt;</span><span class="string">&#x27;2000-01-05&#x27;</span>) e <span class="keyword">left</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.dept_id <span class="operator">=</span> d.id;</span><br></pre></td></tr></table></figure>

<h2 id="事务"><a href="#事务" class="headerlink" title="事务"></a>事务</h2><p><strong>事务</strong>：是一组操作的集合，它是不可分割的最小工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作<strong>要么同时成功，要么同时失败</strong></p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220412000033546.png" alt="image-20220412000033546"></p>
<p><strong>注意：默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式提交事务</strong></p>
<h4 id="事务操作"><a href="#事务操作" class="headerlink" title="事务操作"></a>事务操作</h4><p><strong>案例引入：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> bank(</span><br><span class="line">    id <span class="type">int</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>),</span><br><span class="line">    salary <span class="type">float</span></span><br><span class="line">);</span><br><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> bank <span class="keyword">values</span> (<span class="keyword">null</span>, <span class="string">&#x27;张三&#x27;</span>, <span class="number">2000.0</span>),(<span class="keyword">null</span>,<span class="string">&#x27;李四&#x27;</span>,<span class="number">2000.0</span>);</span><br><span class="line"><span class="comment">-- 转账操作(张三给李四转账1000)</span></span><br><span class="line"><span class="comment">-- 1. 查询张三账户</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> bank <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张三&#x27;</span>;</span><br><span class="line"><span class="comment">-- 2. 将张三的账户余额-1000</span></span><br><span class="line"><span class="keyword">update</span> bank <span class="keyword">set</span> salary<span class="operator">=</span> salary <span class="number">-1000.0</span> <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张三&#x27;</span>;</span><br><span class="line">程序抛出异常</span><br><span class="line"><span class="comment">-- 3. 将李四的账户余额+1000</span></span><br><span class="line"><span class="keyword">update</span> bank <span class="keyword">set</span> salary<span class="operator">=</span>salary<span class="operator">+</span><span class="number">1000.0</span> <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;李四&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p>此时，张三账户金额变少，由于执行出错，李四没有收到转账金额；</p>
<p><strong>操作：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line"># 查看事务</span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@autocommit</span>;</span><br><span class="line"># 设置事务提交方式</span><br><span class="line"><span class="keyword">set</span> @<span class="variable">@autocommit</span> <span class="operator">=</span><span class="number">0</span>; <span class="comment">-- 手动提交：0 ，自动提交：1</span></span><br><span class="line"></span><br><span class="line"># 提交事务</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"></span><br><span class="line">#回滚事务</span><br><span class="line"><span class="keyword">rollback</span>;</span><br></pre></td></tr></table></figure>

<p><strong>改进案例</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 转账操作(张三给李四转账1000)</span></span><br><span class="line"><span class="comment">-- 0. 设置手动提交</span></span><br><span class="line"><span class="keyword">set</span> @<span class="variable">@autocommit</span><span class="operator">=</span><span class="number">0</span>;</span><br><span class="line"><span class="comment">-- 1. 查询张三账户</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> bank <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张三&#x27;</span>;</span><br><span class="line"><span class="comment">-- 2. 将张三的账户余额-1000</span></span><br><span class="line"><span class="keyword">update</span> bank <span class="keyword">set</span> salary<span class="operator">=</span> salary <span class="number">-1000.0</span> <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张三&#x27;</span>;</span><br><span class="line">程序抛出异常</span><br><span class="line"><span class="keyword">rollback</span>;</span><br><span class="line"><span class="comment">-- 3. 将李四的账户余额+1000</span></span><br><span class="line"><span class="keyword">update</span> bank <span class="keyword">set</span> salary<span class="operator">=</span>salary<span class="operator">+</span><span class="number">1000.0</span> <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;李四&#x27;</span>;</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure>

<h4 id="事务的四大特性ACID"><a href="#事务的四大特性ACID" class="headerlink" title="事务的四大特性ACID"></a>事务的四大特性ACID</h4><ul>
<li>原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败；</li>
<li>一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态；</li>
<li>隔离性(Isolation): 数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行；</li>
<li>持久性(Durability)：事务一旦提交或回滚，他对数据库的数据改变就是永久的</li>
</ul>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220412001614588.png" alt="image-20220412001614588"></p>
<h4 id="并发事务问题"><a href="#并发事务问题" class="headerlink" title="并发事务问题"></a>并发事务问题</h4><table>
<thead>
<tr>
<th align="center">问题</th>
<th align="center">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="center">脏读</td>
<td align="center">一个事务读到另外一个事务还没有提交的数据</td>
</tr>
<tr>
<td align="center">不可重复读</td>
<td align="center">一个事务先后读取同一条记录，但两次读取的数据不一致，称为不可重复读</td>
</tr>
<tr>
<td align="center">幻读</td>
<td align="center">一个事务按照条件查询数据时，没有对应的数据行，但是在插入的时候，又发现这行数据存在了，好像出现了‘幻影’</td>
</tr>
</tbody></table>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220412001937820.png" alt="image-20220412001937820"></p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220412002002558.png" alt="image-20220412002002558"></p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220412002029593.png" alt="image-20220412002029593"></p>
<h4 id="事务的隔离级别"><a href="#事务的隔离级别" class="headerlink" title="事务的隔离级别"></a>事务的隔离级别</h4><table>
<thead>
<tr>
<th align="center">隔离级别</th>
<th align="center">脏读</th>
<th align="center">不可重复读</th>
<th align="center">幻读</th>
</tr>
</thead>
<tbody><tr>
<td align="center">Read uncommitted</td>
<td align="center">√</td>
<td align="center">√</td>
<td align="center">√</td>
</tr>
<tr>
<td align="center">Read committed</td>
<td align="center">×</td>
<td align="center">√</td>
<td align="center">√</td>
</tr>
<tr>
<td align="center">Repeatable Read(默认)</td>
<td align="center">×</td>
<td align="center">×</td>
<td align="center">√</td>
</tr>
<tr>
<td align="center">Serializable</td>
<td align="center">×</td>
<td align="center">×</td>
<td align="center">×</td>
</tr>
</tbody></table>
<p><strong>操作：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查看事务的隔离级别</span></span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@transaction</span>_isolation;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 设置事务的隔离级别</span></span><br><span class="line"><span class="keyword">set</span> [session<span class="operator">|</span><span class="keyword">global</span>] transaction isolation level &#123;read uncommitted <span class="operator">|</span> read committed <span class="operator">|</span> repeatable read <span class="operator">|</span> serializable&#125;</span><br></pre></td></tr></table></figure>

<p><strong>案例一：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查看事务的隔离级别</span></span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@transaction</span>_isolation;</span><br><span class="line"><span class="comment">-- 设置事务的隔离级别</span></span><br><span class="line"><span class="keyword">set</span> session transaction isolation level read committed ;</span><br></pre></td></tr></table></figure>

<p><strong>案例二：不可重复读演示</strong></p>
<p><img src="C:\Users\84350\AppData\Roaming\Typora\typora-user-images\image-20220412003719680.png" alt="image-20220412003719680"></p>
 
      <!-- reward -->
      
    </div>
    

    <!-- copyright -->
    
    <div class="declare">
      <ul class="post-copyright">
        <li>
          <i class="ri-copyright-line"></i>
          <strong>版权声明： </strong>
          
          本博客所有文章除特别声明外，著作权归作者所有。转载请注明出处！
          
        </li>
      </ul>
    </div>
    
    <footer class="article-footer">
       
<div class="share-btn">
      <span class="share-sns share-outer">
        <i class="ri-share-forward-line"></i>
        分享
      </span>
      <div class="share-wrap">
        <i class="arrow"></i>
        <div class="share-icons">
          
          <a class="weibo share-sns" href="javascript:;" data-type="weibo">
            <i class="ri-weibo-fill"></i>
          </a>
          <a class="weixin share-sns wxFab" href="javascript:;" data-type="weixin">
            <i class="ri-wechat-fill"></i>
          </a>
          <a class="qq share-sns" href="javascript:;" data-type="qq">
            <i class="ri-qq-fill"></i>
          </a>
          <a class="douban share-sns" href="javascript:;" data-type="douban">
            <i class="ri-douban-line"></i>
          </a>
          <!-- <a class="qzone share-sns" href="javascript:;" data-type="qzone">
            <i class="icon icon-qzone"></i>
          </a> -->
          
          <a class="facebook share-sns" href="javascript:;" data-type="facebook">
            <i class="ri-facebook-circle-fill"></i>
          </a>
          <a class="twitter share-sns" href="javascript:;" data-type="twitter">
            <i class="ri-twitter-fill"></i>
          </a>
          <a class="google share-sns" href="javascript:;" data-type="google">
            <i class="ri-google-fill"></i>
          </a>
        </div>
      </div>
</div>

<div class="wx-share-modal">
    <a class="modal-close" href="javascript:;"><i class="ri-close-circle-line"></i></a>
    <p>扫一扫，分享到微信</p>
    <div class="wx-qrcode">
      <img src="//api.qrserver.com/v1/create-qr-code/?size=150x150&data=http://example.com/2022/05/29/mysql%E5%9F%BA%E7%A1%80/" alt="微信分享二维码">
    </div>
</div>

<div id="share-mask"></div>  
  <ul class="article-tag-list" itemprop="keywords"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/" rel="tag">mysql</a></li></ul>

    </footer>
  </div>

   
  <nav class="article-nav">
    
      <a href="/2022/05/29/hadoop/" class="article-nav-link">
        <strong class="article-nav-caption">上一篇</strong>
        <div class="article-nav-title">
          
            hadoop
          
        </div>
      </a>
    
    
  </nav>

  
   
    
    <script src="https://cdn.staticfile.org/twikoo/1.4.18/twikoo.all.min.js"></script>
    <div id="twikoo" class="twikoo"></div>
    <script>
        twikoo.init({
            envId: ""
        })
    </script>
 
</article>

</section>
      <footer class="footer">
  <div class="outer">
    <ul>
      <li>
        Copyrights &copy;
        2021-2022
        <i class="ri-heart-fill heart_icon"></i> WangQi
      </li>
    </ul>
    <ul>
      <li>
        
      </li>
    </ul>
    <ul>
      <li>
        
        
        <span>
  <span><i class="ri-user-3-fill"></i>访问人数:<span id="busuanzi_value_site_uv"></span></span>
  <span class="division">|</span>
  <span><i class="ri-eye-fill"></i>浏览次数:<span id="busuanzi_value_page_pv"></span></span>
</span>
        
      </li>
    </ul>
    <ul>
      
    </ul>
    <ul>
      
    </ul>
    <ul>
      <li>
        <!-- cnzz统计 -->
        
      </li>
    </ul>
  </div>
</footer>    
    </main>
    <div class="float_btns">
      <div class="totop" id="totop">
  <i class="ri-arrow-up-line"></i>
</div>

<div class="todark" id="todark">
  <i class="ri-moon-line"></i>
</div>

    </div>
    <aside class="sidebar on">
      <button class="navbar-toggle"></button>
<nav class="navbar">
  
  <div class="logo">
    <a href="/"><img src="/images/1.svg" alt="王先生的博客"></a>
  </div>
  
  <ul class="nav nav-main">
    
    <li class="nav-item">
      <a class="nav-item-link" href="/">主页</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/archives">归档</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/categories">分类</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/tags">标签</a>
    </li>
    
    <li class="nav-item">
      <a class="nav-item-link" href="/about">关于我</a>
    </li>
    
  </ul>
</nav>
<nav class="navbar navbar-bottom">
  <ul class="nav">
    <li class="nav-item">
      
      <a class="nav-item-link nav-item-search"  title="搜索">
        <i class="ri-search-line"></i>
      </a>
      
      
    </li>
  </ul>
</nav>
<div class="search-form-wrap">
  <div class="local-search local-search-plugin">
  <input type="search" id="local-search-input" class="local-search-input" placeholder="Search...">
  <div id="local-search-result" class="local-search-result"></div>
</div>
</div>
    </aside>
    <div id="mask"></div>

<!-- #reward -->
<div id="reward">
  <span class="close"><i class="ri-close-line"></i></span>
  <p class="reward-p"><i class="ri-cup-line"></i>请我喝杯咖啡吧~</p>
  <div class="reward-box">
    
    <div class="reward-item">
      <img class="reward-img" src="/images/alipay.jpg">
      <span class="reward-type">支付宝</span>
    </div>
    
    
    <div class="reward-item">
      <img class="reward-img" src="/images/wechat.jpg">
      <span class="reward-type">微信</span>
    </div>
    
  </div>
</div>
    
<script src="/js/jquery-3.6.0.min.js"></script>
 
<script src="/js/lazyload.min.js"></script>

<!-- Tocbot -->
 
<script src="/js/tocbot.min.js"></script>

<script>
  tocbot.init({
    tocSelector: ".tocbot",
    contentSelector: ".article-entry",
    headingSelector: "h1, h2, h3, h4, h5, h6",
    hasInnerContainers: true,
    scrollSmooth: true,
    scrollContainer: "main",
    positionFixedSelector: ".tocbot",
    positionFixedClass: "is-position-fixed",
    fixedSidebarOffset: "auto",
  });
</script>

<script src="https://cdn.staticfile.org/jquery-modal/0.9.2/jquery.modal.min.js"></script>
<link
  rel="stylesheet"
  href="https://cdn.staticfile.org/jquery-modal/0.9.2/jquery.modal.min.css"
/>
<script src="https://cdn.staticfile.org/justifiedGallery/3.8.1/js/jquery.justifiedGallery.min.js"></script>

<script src="/dist/main.js"></script>

<!-- ImageViewer -->
 <!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">

    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>

    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">

        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>

        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">

            <div class="pswp__top-bar">

                <!--  Controls are self-explanatory. Order can be changed. -->

                <div class="pswp__counter"></div>

                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>

                <button class="pswp__button pswp__button--share" style="display:none" title="Share"></button>

                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>

                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>

                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                        <div class="pswp__preloader__cut">
                            <div class="pswp__preloader__donut"></div>
                        </div>
                    </div>
                </div>
            </div>

            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div>
            </div>

            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>

            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>

            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>

        </div>

    </div>

</div>

<link rel="stylesheet" href="https://cdn.staticfile.org/photoswipe/4.1.3/photoswipe.min.css">
<link rel="stylesheet" href="https://cdn.staticfile.org/photoswipe/4.1.3/default-skin/default-skin.min.css">
<script src="https://cdn.staticfile.org/photoswipe/4.1.3/photoswipe.min.js"></script>
<script src="https://cdn.staticfile.org/photoswipe/4.1.3/photoswipe-ui-default.min.js"></script>

<script>
    function viewer_init() {
        let pswpElement = document.querySelectorAll('.pswp')[0];
        let $imgArr = document.querySelectorAll(('.article-entry img:not(.reward-img)'))

        $imgArr.forEach(($em, i) => {
            $em.onclick = () => {
                // slider展开状态
                // todo: 这样不好，后面改成状态
                if (document.querySelector('.left-col.show')) return
                let items = []
                $imgArr.forEach(($em2, i2) => {
                    let img = $em2.getAttribute('data-idx', i2)
                    let src = $em2.getAttribute('data-target') || $em2.getAttribute('src')
                    let title = $em2.getAttribute('alt')
                    // 获得原图尺寸
                    const image = new Image()
                    image.src = src
                    items.push({
                        src: src,
                        w: image.width || $em2.width,
                        h: image.height || $em2.height,
                        title: title
                    })
                })
                var gallery = new PhotoSwipe(pswpElement, PhotoSwipeUI_Default, items, {
                    index: parseInt(i)
                });
                gallery.init()
            }
        })
    }
    viewer_init()
</script> 
<!-- MathJax -->

<!-- Katex -->

<!-- busuanzi  -->
 
<script src="/js/busuanzi-2.3.pure.min.js"></script>
 
<!-- ClickLove -->
 
<script src="/js/clickLove.js"></script>
 
<!-- ClickBoom1 -->

<!-- ClickBoom2 -->

<!-- CodeCopy -->
 
<link rel="stylesheet" href="/css/clipboard.css">
 <script src="https://cdn.staticfile.org/clipboard.js/2.0.10/clipboard.min.js"></script>
<script>
  function wait(callback, seconds) {
    var timelag = null;
    timelag = window.setTimeout(callback, seconds);
  }
  !function (e, t, a) {
    var initCopyCode = function(){
      var copyHtml = '';
      copyHtml += '<button class="btn-copy" data-clipboard-snippet="">';
      copyHtml += '<i class="ri-file-copy-2-line"></i><span>COPY</span>';
      copyHtml += '</button>';
      $(".highlight .code pre").before(copyHtml);
      $(".article pre code").before(copyHtml);
      var clipboard = new ClipboardJS('.btn-copy', {
        target: function(trigger) {
          return trigger.nextElementSibling;
        }
      });
      clipboard.on('success', function(e) {
        let $btn = $(e.trigger);
        $btn.addClass('copied');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-checkbox-circle-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPIED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-checkbox-circle-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
      clipboard.on('error', function(e) {
        e.clearSelection();
        let $btn = $(e.trigger);
        $btn.addClass('copy-failed');
        let $icon = $($btn.find('i'));
        $icon.removeClass('ri-file-copy-2-line');
        $icon.addClass('ri-time-line');
        let $span = $($btn.find('span'));
        $span[0].innerText = 'COPY FAILED';
        
        wait(function () { // 等待两秒钟后恢复
          $icon.removeClass('ri-time-line');
          $icon.addClass('ri-file-copy-2-line');
          $span[0].innerText = 'COPY';
        }, 2000);
      });
    }
    initCopyCode();
  }(window, document);
</script>
 
<!-- CanvasBackground -->
 
<script src="/js/dz.js"></script>
 
<script>
  if (window.mermaid) {
    mermaid.initialize({ theme: "forest" });
  }
</script>


    
    

  </div>
</body>

</html>